package gmusic.dao.impl;

import gmusic.bo.MAGroupeBean;
import gmusic.dao.MAGroupe;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import algutil.connexion.GestionConnexion;

public class MAGroupeImpl implements MAGroupe {

	private static MAGroupeImpl instance;

	public static MAGroupeImpl getInstance() {

		if (instance == null)
			instance = new MAGroupeImpl();
		return instance;
	}

	public void ajouter(int id, String nom) throws SQLException {

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("INSERT INTO ma_groupe (id, nom, creation_date, last_update_date) VALUES (?, ?, now(), now())");
		p.setInt(1, id);
		p.setString(2, nom);
		p.executeUpdate();
		p.close();
		c.close();
	}

	public List<MAGroupeBean> getMAGroupesCommencantPar(String debut)
			throws SQLException {

		List<MAGroupeBean> groupes = new ArrayList<MAGroupeBean>();

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_groupe WHERE nom LIKE '"
						+ debut + "%' ORDER BY nom");
		// p.setString(1, debut);
		ResultSet r = p.executeQuery();

		while (r.next()) {
			groupes.add(new MAGroupeBean(r.getInt("id"), r.getString("nom")));
		}

		return groupes;
	}

	public List<String> getPremieresLettres() throws SQLException {

		List<String> premieresLettres = new ArrayList<String>();

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT DISTINCT substr( nom, 1, 1 ) FROM ma_groupe");
		ResultSet r = p.executeQuery();

		while (r.next()) {
			premieresLettres.add(r.getString(1));
		}

		return premieresLettres;

	}
}
